-module(server).
-export([start/0]).

start() ->
    HostDB = "localhost",
    UserDB = "root",
    PasswordDB = "root",
    Database = "test11",
    
    {ok, Pid} = mysql:start_link([{host, HostDB}, {user, UserDB}, {password, PasswordDB}]),
    
    io:format("Connected to MySQL server.~n"),
    log("Connected to MySQL server \n"),
    Query = io_lib:format("USE ~s", [Database]),	
    case mysql:query(Pid, Query) of
	    ok -> io:format("Database exists ~n");  
	    _ -> start_database(Database, Pid)
    end,
    
    file:delete("debug.txt"),
    {ok, Socket} = gen_udp:open(20231, [binary, {active, true}]),
    io:format("Server started~n", []),
    log("Server started \n"),
    loop(Socket, Pid, Database).

loop(Socket, Pid, Database) ->
    receive
        {udp, Socket, Host, Port, Data} ->
            Msg = binary_to_list(Data),
            case string:tokens(Msg, "|") of
                ["newuser", Login, Password_hash] ->
                    ok = mysql:query(Pid, "INSERT INTO Users (login, password_Hash) VALUES (?, ?)", [Login, Password_hash]),
                    Result = mysql:query(Pid, "SELECT id FROM Users WHERE login = ?", [Login]),
		    io:format("~p", [Result]),
		    Value = get_value(Result),
	            io:format("Значение: ~p~n", [Value]),
		    gen_udp:send(Socket, Host, Port, Value),
                    io:format("new user!~n"),
                    log("new user!~n");
                ["verify", Login, Password_hash] -> 
                    Result = mysql:query(Pid, "SELECT id FROM Users WHERE login = ? AND password_hash = ?", [Login, Password_hash]),
                    io:format("~p", [Result]),
                    Value = get_value(Result),
                    io:format("Значение: ~p~n", [Value]),
                    case Value of
                    	V when is_number(V) ->
                    		gen_udp:send(Socket, Host, Port, Value),
                    		io:format("verify!~n");
                        _ ->
                        	io:format("verify error!~n"),
                        	gen_udp:send(Socket, Host, Port, "verify error!~n")
                    end;
	        ["getcontact", Login] ->
	             Result = mysql:query(Pid, "SELECT id, login FROM Users WHERE login = ?", [Login]),
	             io:format("~p~n", [Result]),
	             FormattedResult = lists:flatten(io_lib:format("~p", [Result])),
	             io:format("~p~n", [FormattedResult]),
	             gen_udp:send(Socket, Host, Port, FormattedResult);
	        ["getmessages", ReciveID, GetID] ->
	             Result = mysql:query(Pid, "SELECT Time, Message FROM MessageLog WHERE ReciveID = ? AND GetID = ?", [ReciveID, GetID]),
          	      %io:format("~p~n", [Result]),
    		     FormattedResult = format_result(Result),
    	             io:format("~p~n", [FormattedResult]),
    	             gen_udp:send(Socket, Host, Port, FormattedResult),
    	             Result2 = mysql:query(Pid, "SELECT Time, Message FROM MessageLog WHERE ReciveID = ? AND GetID = ?", [GetID, ReciveID]),
    	             FormattedResult2 = format_result(Result2),
    	             io:format("~p~n", [FormattedResult2]),
    	             gen_udp:send(Socket, Host, Port, "You:" ++ FormattedResult2);
                ["insert", Time, ReciveID, GetID, Message] ->
                    data_add(Time, ReciveID, GetID, Message, Pid, Database),
                    io:format("new message!~n"),
                    log("new message!~n");
                _ ->
                    io:format("error!~n"),
                    log("error!~n")
            end,
            loop(Socket, Pid, Database)
    end.

result_to_list({ok, _, [Result]}) ->
    lists:map(fun erlang:binary_to_list/1, Result);
result_to_list(_) ->
    [].

format_result({ok, [<<"Time">>, <<"Message">>], Data}) ->
    FormattedData = format_data(Data),
    lists:flatten(io_lib:format("~p", [FormattedData])).

format_data(Data) ->
    lists:map(fun format_item/1, Data).

format_item([Time, Message]) ->
    {TimeStr, MessageStr} = {binary_to_list(Time), binary_to_list(Message)},
    [TimeStr, MessageStr].

get_value({ok, _, [[Value]]}) ->
    Value;
get_value(_) ->
    undefined.
    
log(Message) ->
    Timestamp = erlang:universaltime(),
    FormattedTimestamp = format_timestamp(Timestamp),
    Entry = "[" ++ FormattedTimestamp ++ "] " ++ Message ++ "\n",
    file:write_file("debug.txt", Entry, [append]).

format_timestamp(Timestamp) ->
    {{Year, Month, Day}, {Hour, Minute, Second}} = calendar:universal_time_to_local_time(Timestamp),
    lists:flatten(io_lib:format("~4..0w-~2..0w-~2..0w ~2..0w:~2..0w:~2..0w", [Year, Month, Day, Hour, Minute, Second])).
    
start_database(Database, Pid) ->
	Query = io_lib:format("CREATE DATABASE IF NOT EXISTS ~s", [Database]),
	ok = mysql:query(Pid, Query),
	QueryUse = io_lib:format("USE ~s", [Database]),
	ok = mysql:query(Pid, QueryUse),
	ok = mysql:query(Pid, "CREATE TABLE IF NOT EXISTS Users (id INT AUTO_INCREMENT PRIMARY KEY, login VARCHAR(255) UNIQUE, password_Hash VARCHAR(255)); CREATE TABLE IF NOT EXISTS MessageLog (Transaction INT AUTO_INCREMENT PRIMARY KEY, Time TEXT NOT NULL, ReciveID INT NOT NULL, GetID INT NOT NULL, Message LONGTEXT, FOREIGN KEY (ReciveID) REFERENCES Users(id), FOREIGN KEY (GetID) REFERENCES Users(id))"),
	io:format("Database was Created ~n"),
	mysql:stop(Pid),
	start().
	

	


data_add(Time, ReciveID, GetID, Message, Pid, Database) ->
	QueryUse = io_lib:format("USE ~s", [Database]),
	ok = mysql:query(Pid, QueryUse),
	ok = mysql:query(Pid,"INSERT INTO MessageLog (Time, ReciveID, GetID, Message) VALUES (?, ?, ?, ?)", [Time, ReciveID, GetID, Message]).
	

	
